﻿using Dapper;
using Domain.Entity;
using Domain.Entity.zct;
using Domain.IRepository;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;

namespace EFCore.Repository
{
    public class UserRepository : IUserRepository
    {

        //连接字符串
        string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";


        //查询所有用户
        public async Task<List<User>> GetAllUser()
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select user_id,user_name from [dbo].[zct_user] where is_delete=0";
                return sqlConnection.Query<User>(sql).ToList();
            }
        }


        //登录
        public async Task<UserRole> GetUser(string account, string pwd)
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select UR.user_role_id, U.user_id,R.role_id  from [dbo].[zct_user] U 
                                inner join [dbo].[zct_user_role] UR on U.user_id=UR.user_id
                                inner join [dbo].[zct_role] R on R.role_id=UR.role_id
                                where user_account='{account}' and user_password='{pwd}' and  user_status=0 and U.is_delete=0";
                return await sqlConnection.QueryFirstOrDefaultAsync<UserRole>(sql);
            }
        }


        //根据部门id查询对应用户信息
        public async Task<List<User>> GetUserByDeptId(int depid)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select * from [dbo].[zct_user] where department_id={depid}";
                return sqlConnection.Query<User>(sql).ToList();
            }
        }

        //根据用户id查询用户
        public async Task<User> GetUserById(int uid)
        {
            using (SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select U.*,department_name,position_name from [dbo].[zct_user] U
                                inner join [dbo].[zct_department] D on D.department_id=U.department_id
                                inner join [dbo].[zct_position] P on P.position_id=U.position_id
                                where user_id={uid}";
                return await sqlConnection.QueryFirstOrDefaultAsync<User>(sql);
            }
        }


        //修改个人信息
        public async Task<int> UpdateInfo(string? name, string? sex, string? phone, string? avatar, int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[zct_user] set user_name='{name}',user_sex='{sex}',user_phone='{phone}',user_avatar='{avatar}' where user_id={id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //修改密码
        public async Task<int> UpdatePwd(int uid, string pwd)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[zct_user] set user_password='{pwd}' where user_id={uid}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //全部查
        public async Task<FenYe<User>> GetAll(FenYe<User> fenYe)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 构建查询条件
                string whereBuilder = $" is_delete=0 ";
                var parameters = new DynamicParameters();

                // 分页参数
                parameters.Add("@pageSize", 10);
                parameters.Add("@keyName", "position_sort"); // 主键字段
                parameters.Add("@tabelName", "zct_user");
                parameters.Add("@currentPage", fenYe.CurrentPage);
                parameters.Add("@where", whereBuilder);
                parameters.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);

                // 执行存储过程
                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                var result = await sqlConnection.QueryAsync<User>(sql, parameters);

                // 设置分页结果
                fenYe.List = result.ToList();
                fenYe.Rows = parameters.Get<int>("@rows");
                return fenYe;
            }
        }

        //条件查
        public async Task<FenYe<User>> GetCondition(FenYe<User> fenYe, string? User_account, string? User_name, int? Position_id, int? User_status)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 构建查询条件
                string whereBuilder = $" is_delete=0 ";
                var parameters = new DynamicParameters();

                if (!string.IsNullOrEmpty(User_account))
                {
                    whereBuilder += $" and User_account like '%{User_account}%'";
                }
                if (!string.IsNullOrEmpty(User_name))
                {
                    whereBuilder += $" and User_name like '%{User_name}%'";
                }
                if (Position_id.HasValue)
                {
                    whereBuilder += $" and Position_id = {Position_id}";
                }
                if (User_status.HasValue)
                {
                    whereBuilder += $" and User_status = {User_status}";
                }

                // 分页参数
                parameters.Add("@pageSize", 10);
                parameters.Add("@keyName", "user_sort"); // 主键字段
                parameters.Add("@tabelName", "zct_user");
                parameters.Add("@currentPage", fenYe.CurrentPage);
                parameters.Add("@where", whereBuilder);
                parameters.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);

                // 执行存储过程
                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                var result = await sqlConnection.QueryAsync<User>(sql, parameters);

                // 设置分页结果
                fenYe.List = result.ToList();
                fenYe.Rows = parameters.Get<int>("@rows");
                return fenYe;
            }
        }

        //id查询
        public async Task<User> GetID(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = "select user_id,User_sex,User_phone,user_password,User_account,user_name,user_status,Position_id,Department_id,user_sort,remark from [dbo].[zct_user] where is_delete=0 AND user_id = @id";

                var parameters = new DynamicParameters();
                parameters.Add("@id", id);

                return await sqlConnection.QueryFirstOrDefaultAsync<User>(sql, parameters);
            }
        }

        //停用
        public async Task<int> GetDeactivated(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[zct_user] set User_status=1 where user_id={id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        //启用
        public async Task<int> GetEnable(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[zct_user] set User_status=0 where user_id={id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        public async Task<int> GetEdit(int[] Role_id, User user)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                // 使用事务确保数据一致性
                using (var transaction = sqlConnection.BeginTransaction())
                {
                    try
                    {
                        // 1. 更新用户基本信息
                        var updateUserSql = @"
                            UPDATE zct_user
                            SET 
                                User_account = @User_account,
                                User_password = @User_password,
                                User_name = @User_name,
                                User_sex = @User_sex,
                                User_phone = @User_phone,
                                Position_id = @Position_id,
                                Department_id = @Department_id,
                                User_status = @User_status,
                                User_sort = @User_sort,
                                Remark = @Remark
                            WHERE User_id = @User_id;";

                        var userParams = new DynamicParameters();
                        userParams.Add("@User_id", user.User_id);
                        userParams.Add("@User_account", user.User_account);
                        userParams.Add("@User_password", user.User_password);
                        userParams.Add("@User_name", user.User_name);
                        userParams.Add("@User_sex", user.User_sex);
                        userParams.Add("@User_phone", user.User_phone);
                        userParams.Add("@Position_id", user.Position_id);
                        userParams.Add("@Department_id", user.Department_id);
                        userParams.Add("@User_status", user.User_status);
                        userParams.Add("@User_sort", user.User_sort);
                        userParams.Add("@Remark", user.Remark);

                        int rowsAffected = await sqlConnection.ExecuteAsync(
                            updateUserSql,
                            userParams,
                            transaction: transaction
                        );

                        // 2. 删除该用户原有的所有角色关联
                        var deleteRolesSql = @"
                            DELETE FROM zct_user_role 
                            WHERE User_id = @UserId";

                        await sqlConnection.ExecuteAsync(
                            deleteRolesSql,
                            new { UserId = user.User_id },
                            transaction: transaction
                        );

                        // 3. 插入新的角色关联
                        if (Role_id != null && Role_id.Length > 0)
                        {
                            var insertRolesSql = @"
                                INSERT INTO zct_user_role (
                                    User_id,
                                    Role_id
                                ) VALUES (
                                    @UserId,
                                    @RoleId
                                )";

                            foreach (int roleId in Role_id)
                            {
                                await sqlConnection.ExecuteAsync(
                                    insertRolesSql,
                                    new { UserId = user.User_id, RoleId = roleId },
                                    transaction: transaction
                                );
                            }
                        }

                        // 提交事务
                        transaction.Commit();
                        return rowsAffected;
                    }
                    catch
                    {
                        // 回滚事务
                        transaction.Rollback();
                        throw; // 重新抛出异常
                    }
                }
            }
        }

        public async Task<int> GetAdd(int[] Role_id, User user)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                // 使用事务确保数据一致性
                using (var transaction = sqlConnection.BeginTransaction())
                {
                    try
                    {
                        // 1. 插入用户数据并获取生成的User_id
                        var userSql = @"
                            INSERT INTO zct_user (
                                User_account,
                                User_name,
                                User_password,
                                User_sex,
                                User_phone,
                                Position_id,
                                Department_id,
                                User_status,
                                User_sort,
                                Remark
                            ) 
                            OUTPUT INSERTED.User_id
                            VALUES (
                                @User_account,
                                @User_name,
                                @User_password,
                                @User_sex,
                                @User_phone,
                                @Position_id,
                                @Department_id,
                                @User_status,
                                @User_sort,
                                @Remark
                            );";

                        var userParams = new DynamicParameters();
                        userParams.Add("@User_account", user.User_account);
                        userParams.Add("@User_name", user.User_name);
                        userParams.Add("@User_password", user.User_password);
                        userParams.Add("@User_sex", user.User_sex);
                        userParams.Add("@User_phone", user.User_phone);
                        userParams.Add("@Position_id", user.Position_id);
                        userParams.Add("@Department_id", user.Department_id);
                        userParams.Add("@User_status", user.User_status);
                        userParams.Add("@User_sort", user.User_sort);
                        userParams.Add("@Remark", user.Remark);

                        // 执行插入并获取新用户的ID
                        int userId = await sqlConnection.ExecuteScalarAsync<int>(
                            userSql,
                            userParams,
                            transaction: transaction
                        );

                        // 2. 插入用户角色关联数据
                        if (Role_id != null && Role_id.Length > 0)
                        {
                            var roleSql = @"
                            INSERT INTO zct_user_role (
                                User_id,
                                Role_id
                            ) VALUES (
                                @UserId,
                                @RoleId
                            )";

                            foreach (int roleId in Role_id)
                            {
                                await sqlConnection.ExecuteAsync(
                                    roleSql,
                                    new { UserId = userId, RoleId = roleId },
                                    transaction: transaction
                                );
                            }
                        }

                        // 提交事务
                        transaction.Commit();
                        return userId;
                    }
                    catch
                    {
                        // 回滚事务
                        transaction.Rollback();
                        throw; // 重新抛出异常
                    }
                }
            }
        }

        //删除
        public async Task<int> GetDelete(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[zct_user] set is_delete=1 where user_id={id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }
    }
}
